﻿CREATE PROCEDURE [dbo].[proc_Customer_ExtractBD]
	(
		@UserName varchar(20),
		@IndustryOneId varchar(20),
		@IndustryTwoId varchar(20),
		@rndID varchar(200)
	)
AS	
Begin
	Declare @companyid int,@num int,@Days int
	Declare	@Sql varchar(3000)
	Declare @Extract int

	Set @Days = -10	--10日内批注过的客户不再参与申领
	Select @Extract=DateDiff(dd,'1900-1-1',getdate())	--申领日期(今天)
	EXEC @companyid=proc_Company_GetID @UserName,@companyid--单位ID
	Select @num=Count(Id) From Customer Where ProtectName=@UserName And ProtectId=3 And Companyid=@companyid And ExtractDate<=@Extract 
	and  customerid in ( select distinct cusid from [Project] where [ProductId]=1 and Companyid=@companyid)--用户现有的公库客户数(不含自己当日新录入的客户)
	
	If @num=0
	Begin
		If @IndustryOneId='0'	--所有行业
		Begin
			Set @Sql=';WITH list As(Select ROW_NUMBER() OVER (Order By newid()) AS Row,
				id From Customer Where Companyid='+Convert(varchar(10),@companyid)+' and Audit=1 and UpTime<DateAdd(day,'+Convert(varchar(10),@Days)+',GetDate()) And
				(ProtectName is null) and ProtectId=3 And [delFlag]=0 And (CustomerName Not like ''%免打扰%'')
				and  customerid in ( select distinct cusid from [Project] where [ProductId]=1 and Companyid='+Convert(varchar(10),@companyid)+')
				) '

			Set @Sql = @Sql + ' Update a Set a.ProtectId=1,a.ProtectName='''+@UserName+''',ExtractDate='+Convert(varchar(10),@Extract)+' From Customer a,(Select Id From list Where Row In('+@rndID+')) b Where a.id=b.id'
			Exec(@Sql)
			RETURN @@rowcount
		End
		Else	--指定的一级行业
		Begin
			Set @Sql=';WITH list As(Select ROW_NUMBER() OVER (Order By newid()) AS Row,
					id From Customer Where Companyid='+Convert(varchar(10),@companyid)+' and Audit=1 and UpTime<DateAdd(day,'+Convert(varchar(10),@Days)+',GetDate()) And
					(ProtectName is null) and ProtectId=3 And [delFlag]=0 And (CustomerName Not like ''%免打扰%'') And 
					 customerid in ( select distinct cusid from [Project] where [ProductId]=1 and Companyid='+Convert(varchar(10),@companyid)+') and 
					'
					

			If @IndustryTwoId='0'	--所有指定的一级行业
				Set @Sql = @Sql + 'IndustryOneId=' + @IndustryOneId + ')'
			Else	--指定的二级行业
				Set @Sql = @Sql + 'IndustryTwoId=' + @IndustryTwoId + ')'

			Set @Sql = @Sql + ' Update a Set a.ProtectId=1,a.ProtectName='''+@UserName+''',ExtractDate='+Convert(varchar(10),@Extract)+' From Customer a,(Select Id From list Where Row In('+@rndID+')) b Where a.id=b.id'
			Exec(@Sql)
			RETURN @@rowcount
		End
	End
End
